import io
import openpyxl
import os
import datetime
from qcloud_cos import CosS3Client, CosServiceError
from fastapi import HTTPException
from src.models import FillRequest, FillInstruction
from src.config import settings

# Define the directory where templates are stored
TEMPLATE_DIR = os.path.join(os.path.dirname(os.path.dirname(__file__)), "..", "templates")

if not os.path.exists(TEMPLATE_DIR):
    os.makedirs(TEMPLATE_DIR)
    print(f"Created template directory: {TEMPLATE_DIR}")

async def process_excel_filling(
    fill_data: FillRequest,
    cos_client: CosS3Client
) -> dict:
    """
    Handles the core logic of filling an Excel template and uploading to COS.
    """
    template_name = fill_data.template_name
    template_path = os.path.join(TEMPLATE_DIR, template_name)

    if not os.path.exists(template_path):
        raise HTTPException(status_code=404, detail=f"Template '{template_name}' not found.")
    if not template_path.lower().endswith(".xlsx"):
        raise HTTPException(status_code=400, detail="Invalid template file type. Only .xlsx files are supported.")

    try:
        workbook = openpyxl.load_workbook(template_path)

        for sheet_name, instructions_list in fill_data.sheet_instructions.items():
            if sheet_name not in workbook.sheetnames:
                print(f"Warning: Sheet '{sheet_name}' not found in template '{template_name}'. Skipping instructions for this sheet.")
                continue

            sheet = workbook[sheet_name]

            for instruction in instructions_list:
                if instruction.type == "placeholder":
                    # 遍历所有单元格以查找占位符
                    for row_idx in range(1, sheet.max_row + 1):
                        for col_idx in range(1, sheet.max_column + 1):
                            cell = sheet.cell(row=row_idx, column=col_idx)
                            if isinstance(cell.value, str):
                                for key, value in instruction.data.items():
                                    placeholder = f"{{{{{key}}}}}"
                                    if placeholder in cell.value:
                                        cell.value = cell.value.replace(placeholder, str(value))
                elif instruction.type == "cell":
                    for cell_address, value in instruction.data.items():
                        try:
                            sheet[cell_address] = value
                        except Exception as e:
                            print(f"Warning: Could not set cell '{cell_address}' in sheet '{sheet_name}' of template '{template_name}'. Error: {e}")

        output_stream = io.BytesIO()
        workbook.save(output_stream)
        output_stream.seek(0)

        # Generate a unique filename for the uploaded file
        original_filename_base, original_extension = os.path.splitext(template_name)
        safe_base_name = "".join(c if c.isalnum() or c in ['-', '_', '.'] else "_" for c in original_filename_base)
        timestamp = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
        cos_key = f"filled_excel/{safe_base_name}_{timestamp}{original_extension}"

        try:
            cos_client.put_object(
                Bucket=settings.COS_BUCKET_NAME,
                Body=output_stream.getvalue(),
                Key=cos_key,
                ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            )
            print(f"Successfully uploaded {cos_key} to COS.")

            cos_file_url = f"https://{settings.COS_BUCKET_NAME}.cos.{settings.COS_REGION}.myqcloud.com/{cos_key}"
            ci_download_url = f"https://{settings.COS_BUCKET_NAME}-{settings.COS_APP_ID}.ci.{settings.COS_REGION}.myqcloud.com/{cos_key}"

            return {
                "message": "File filled and uploaded successfully!",
                "cos_file_url": cos_file_url,
                "ci_download_url": ci_download_url,
                "cos_key": cos_key
            }
        except CosServiceError as e:
            print(f"COS Service Error: {e.get_error_code()} - {e.get_error_msg()}")
            raise HTTPException(status_code=500, detail=f"Failed to upload to COS: {e.get_error_msg()}")
        except Exception as e:
            print(f"An unexpected error occurred during COS upload: {e}")
            raise HTTPException(status_code=500, detail=f"An unexpected error occurred during upload: {e}")
    except Exception as e:
        print(f"An error occurred during Excel processing: {e}")
        raise HTTPException(status_code=500, detail=f"An error occurred during Excel processing: {e}") 